import mysql.connector
import matplotlib.pyplot as plt
import pandas as pd

# 配置MySQL数据库连接参数
config = {
    'user': 'root',       # 数据库用户名
    'password': 'kyds2333', # 数据库密码
    'host': 'localhost',  # 数据库主机地址
    'database': 'pythonfinal'  # 数据库名称
}

# 连接到MySQL数据库
conn = mysql.connector.connect(**config)
cursor = conn.cursor()

# 查询总题目数量
cursor.execute("SELECT COUNT(*) AS total_questions FROM questions")
total_questions = cursor.fetchone()[0]

# 查询每个知识点的题目数量
query = """
SELECT kp.name AS knowledge_point, COUNT(q.id) AS question_count
FROM questions q
JOIN knowledge_points kp ON q.knowledge_point_id = kp.id
GROUP BY kp.id
ORDER BY kp.name;
"""
cursor.execute(query)
knowledge_point_counts = cursor.fetchall()

# 关闭数据库连接
conn.close()

# 打印查询结果
print(f"总题目数量: {total_questions}")
for kp, count in knowledge_point_counts:
    print(f"知识点 '{kp}' 包含的题目数量: {count}")

# 转换为pandas DataFrame
df = pd.DataFrame(knowledge_point_counts, columns=['knowledge_point', 'question_count'])

# 设置Matplotlib以支持中文显示
plt.rcParams['font.sans-serif'] = ['SimHei']  # 使用黑体
plt.rcParams['axes.unicode_minus'] = False    # 正常显示负号

# 绘制柱状图
plt.figure(figsize=(10, 6))
plt.bar(df['knowledge_point'], df['question_count'], color='skyblue')

# 标注每个柱子上的数量
for index, value in enumerate(df['question_count']):
    plt.text(index, value, str(value), ha='center', va='bottom')

# 设置图表的标题和标签
plt.xlabel('知识点')
plt.ylabel('题目数量')
plt.title(f'各知识点的题目数量 (总题目数量: {total_questions})')
plt.xticks(rotation=45)

# 显示图表
plt.grid(axis='y', linestyle='--', alpha=0.7)
plt.tight_layout()  # 调整布局以防止标签重叠
plt.show()
